package com.neuedu.curd.dao;

/**
 * 项目：spring-java4
 * 创建时间：  2022-03-21   14:02
 * 作者 :jshand
 * 描述 : Dao
 */


import com.neuedu.curd.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Repository
public class UserDao {


    @Autowired
    JdbcTemplate jdbcTemplate;

    public List<User> selectList(){

        String sql = "select * from user ";

        List users = jdbcTemplate.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setTelephone(rs.getString("telephone"));
                user.setRealname(rs.getString("realname"));
                user.setActive(rs.getInt("active"));
                user.setCreateTime(rs.getTimestamp("createtime"));
                return user;
            }
        });
        return users;
    }


    /**
     * 根据主键 查询一个数据
     * @param userId
     * @return
     */
    public User selectById(Integer userId ){

        String sql = "select * from user  where user_id = ?";

        User user = jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setRealname(rs.getString("realname"));
                user.setActive(rs.getInt("active"));
                user.setCreateTime(rs.getTimestamp("createtime"));
                return user;
            }
        },userId);
        return user;
    }


    /**
     * 查询唯一结果用的
     * @return
     */
    public int selectCount(){
        String sql = " select count(0) from user";
        Integer count = jdbcTemplate.queryForObject(sql, Integer.class);
        return count;
    }




    public int insert(User user){
        StringBuilder sql = new StringBuilder();
        sql.append("insert into user (`username`, `password`,  `realname`, `telephone`, `active`, `createtime`)");
        sql.append("values( ?,?,?,?,?,?)");

        Object[] paras = new Object[]{user.getUsername(),
                user.getPassword(),
                user.getRealname(),
                user.getTelephone(),
                user.getActive(),
                user.getCreateTime()};


        return jdbcTemplate.update(sql.toString(), paras  );

    }


    public int update(User user){

        String sql  =" UPDATE   `user`  SET `username` = ?, `password` = ?, `realname` = ?," +
                " `telephone` = ?, `active` = ? , `createtime` = ?  WHERE `user_id` = ? ";

        return jdbcTemplate.update(sql,
                user.getUsername(),
                user.getPassword(),
                user.getRealname(),
                user.getTelephone(),
                user.getActive(),
                user.getCreateTime(),

                user.getUserId()
        );

    }


    public int deleteById(Integer id) {
        String sql  ="  delete from user where user_id = ? ";

        return jdbcTemplate.update(sql,id  );
    }
}
